﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FlorDeLiz.BusinessEntitites;
using MySql.Data.MySqlClient;
using Constants = FlorDeLiz.Utils.Constants;
using System.Data;

namespace FlorDeLiz.DataAccess
{

    public class ContasPagarDA : BaseDA
    {
        #region Singleton
        private static ContasPagarDA instancia;
        public static ContasPagarDA Instancia
        {
            get
            {
                if (instancia == null)
                {
                    instancia = new ContasPagarDA();
                }
                return instancia;
            }
        }
        #endregion


        public List<ContasPagar> ObterContasPagar(int? id, string descricao, DateTime? dataInicio, DateTime? dataFim)
        {
            List<ContasPagar> contas = new List<ContasPagar>();
            try
            {
                if (Conexao.State != ConnectionState.Open)
                {
                    Conexao.Open();
                }
                CommandSql = new MySqlCommand(Constants.Procedures.SP_SELECT_CONTAS_PAGAR, Conexao);

                if (id.HasValue)
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_ID_CONTA", MySqlDbType.Int32, 11, ParameterDirection.Input, id.Value));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_ID_CONTA", MySqlDbType.Int32, 11, ParameterDirection.Input, DBNull.Value));
                }

                if (!string.IsNullOrEmpty(descricao))
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DSC_CONTA", MySqlDbType.String, 50, ParameterDirection.Input, descricao));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DSC_CONTA", MySqlDbType.String, 50, ParameterDirection.Input, DBNull.Value));
                }

                if (dataInicio.HasValue && dataFim.HasValue)
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_INICIO", MySqlDbType.DateTime, 0, ParameterDirection.Input, dataInicio));
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_FIM", MySqlDbType.DateTime, 0, ParameterDirection.Input, dataFim));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_INICIO", MySqlDbType.DateTime, 0, ParameterDirection.Input, DBNull.Value));
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_FIM", MySqlDbType.DateTime, 0, ParameterDirection.Input, DBNull.Value));
                }

                CommandSql.CommandType = System.Data.CommandType.StoredProcedure;
                using (MySqlDataReader reader = CommandSql.ExecuteReader())
                {
                    contas = PreencherContas(reader);
                }
            }
            catch (Exception ex)
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
                throw ex;
            }
            finally
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
            }
            return contas;
        }

        private List<ContasPagar> PreencherContas(MySqlDataReader reader)
        {
            List<ContasPagar> contas = new List<ContasPagar>();
            ContasPagar conta;
            while (reader.Read())
            {
                conta = new ContasPagar();
                conta.Id = (int)reader["ID_CONTA"];
                conta.Codigo = reader["COD_CONTA"].ToString();
                conta.Descricao = reader["DSC_CONTA"].ToString();
                conta.DataVencimento = (DateTime)reader["DT_VENCIMENTO"];
                conta.Valor = (decimal)reader["VLR_CONTA"];

                if (reader["DT_PAGAMENTO"] == DBNull.Value)
                {
                    conta.DataPagmaneto = null;
                }
                else
                {
                    conta.DataPagmaneto = (DateTime)reader["DT_PAGAMENTO"];
                }

                if (reader["VLR_CONTA_PAGO"] == DBNull.Value)
                {
                    conta.ValorPago = null;
                }
                else
                {
                    conta.ValorPago = (decimal)reader["VLR_CONTA_PAGO"];
                }
                
                contas.Add(conta);
            }

            return contas;
        }

        public ContasPagar InserirContaPagar(ContasPagar conta)
        {
            try
            {
                if (Conexao.State != ConnectionState.Open)
                {
                    Conexao.Open();
                }

                CommandSql = new MySqlCommand(Constants.Procedures.SP_INSERT_CONTAS_PAGAR, Conexao);

                CommandSql.Parameters.Add(AdicionarParametro("P_COD_CONTA", MySqlDbType.String, 10, ParameterDirection.Input, conta.Codigo));
                CommandSql.Parameters.Add(AdicionarParametro("P_DSC_CONTA", MySqlDbType.String, 50, ParameterDirection.Input, conta.Descricao));
                CommandSql.Parameters.Add(AdicionarParametro("P_VLR_CONTA", MySqlDbType.Decimal, 0, ParameterDirection.Input, conta.Valor));
                CommandSql.Parameters.Add(AdicionarParametro("P_DT_VENCIMENTO", MySqlDbType.DateTime, 0, ParameterDirection.Input, conta.DataVencimento));
                if (conta.ValorPago.HasValue)
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_VLR_CONTA_PAGO", MySqlDbType.Decimal, 0, ParameterDirection.Input, conta.ValorPago.Value));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_VLR_CONTA_PAGO", MySqlDbType.Decimal, 0, ParameterDirection.Input, DBNull.Value));
                }

                if (conta.DataPagmaneto.HasValue)
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_PAGAMENTO", MySqlDbType.DateTime, 0, ParameterDirection.Input, conta.DataPagmaneto.Value));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_PAGAMENTO", MySqlDbType.DateTime, 0, ParameterDirection.Input, DBNull.Value));
                }
                
               
                CommandSql.CommandType = CommandType.StoredProcedure;
                using (MySqlDataReader reader = CommandSql.ExecuteReader())
                {
                    List<ContasPagar> contas = PreencherContas(reader);
                    if (contas != null && contas.Count > 0)
                    {
                        conta = contas[0];
                    }
                }
            }
            catch (Exception ex)
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
                throw ex;
            }
            finally
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
            }

            return conta;
        }

        public ContasPagar AlterarContaPagar(ContasPagar conta)
        {
            try
            {
                if (Conexao.State != ConnectionState.Open)
                {
                    Conexao.Open();
                }

                CommandSql = new MySqlCommand(Constants.Procedures.SP_UPDATE_CONTAS_PAGAR, Conexao);

                CommandSql.Parameters.Add(AdicionarParametro("P_ID_CONTA", MySqlDbType.Int32, 4, ParameterDirection.Input, conta.Id));
                CommandSql.Parameters.Add(AdicionarParametro("P_COD_CONTA", MySqlDbType.String, 10, ParameterDirection.Input, conta.Codigo));
                CommandSql.Parameters.Add(AdicionarParametro("P_DSC_CONTA", MySqlDbType.String, 50, ParameterDirection.Input, conta.Descricao));
                CommandSql.Parameters.Add(AdicionarParametro("P_VLR_CONTA", MySqlDbType.Decimal, 0, ParameterDirection.Input, conta.Valor));
                CommandSql.Parameters.Add(AdicionarParametro("P_DT_VENCIMENTO", MySqlDbType.DateTime, 0, ParameterDirection.Input, conta.DataVencimento));
                if (conta.ValorPago.HasValue)
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_VLR_CONTA_PAGO", MySqlDbType.Decimal, 0, ParameterDirection.Input, conta.ValorPago.Value));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_VLR_CONTA_PAGO", MySqlDbType.Decimal, 0, ParameterDirection.Input, DBNull.Value));
                }

                if (conta.DataPagmaneto.HasValue)
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_PAGAMENTO", MySqlDbType.DateTime, 0, ParameterDirection.Input, conta.DataPagmaneto.Value));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_DT_PAGAMENTO", MySqlDbType.DateTime, 0, ParameterDirection.Input, DBNull.Value));
                }
                CommandSql.CommandType = CommandType.StoredProcedure;

                CommandSql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
                throw ex;
            }
            finally
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
            }

            return conta;
        }
    }
}
